/*
 * Copyright 2009 Zero Separation
 *
 *     This file is part of PDSSQLService.
 *
 *  PDSSQLService is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU Lesser General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  PDSSQLService is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU Lesser General Public License for more details.
 *
 *  You should have received a copy of the GNU Lesser General Public License
 *  along with PDSSQLService.  If not, see <http://www.gnu.org/licenses/>.
 *
 */


package com.zero_separation.pds.sql;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.RowId;
import java.sql.Time;
import java.sql.Timestamp;
import java.net.URL;
import java.sql.Date;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * An SQLStatement holds all of the information required to execute an SQL
 * statement on a connected SQL database.
 *
 * @author Tim Boura - Zero Separation
 */
public class SQLStatement implements Serializable {

    /** The version of the serialized form of this class. */
    private static final long serialVersionUID = 1L;

    /** The sql query string. */
    private String statement;

    /** The paramaters for the SQL query. */
    private List<SQLParam> params;

    /** The SQL query flags. */
    private int SQLQueryFlags = 0;

    /** Used for debugging purposes to display useful string names for SQL types. */
    private static final Map<Integer, String> SQLTypeNames;
    static {
        // Use reflection to fill the map with the contents of all of the integer
        // fields in the java.sql.Types class.
        SQLTypeNames = new HashMap();
        for (Field field: java.sql.Types.class.getFields())
            try {
                SQLTypeNames.put(field.getInt(null), field.getName());
            } catch (IllegalAccessException e) {
            } catch (IllegalArgumentException e) {
            }
    }

    /**
     * This method returns the list of parameters used by this statement.
     * 
     * @return An unmodifiable view of the list of paramaters
     */
    public List<SQLParam> getParams() {
        return Collections.unmodifiableList(params);
    }

    /**
     * This utility function returns a programmer-readable string description of
     * the SQL type integer passed into it.
     *
     * @param SQLType The integer value
     * @return A string describing the type (e.g. "BOOLEAN", "FLOAT", etc)
     */
    public static String getSQLTypeName(int SQLType) {
        return SQLTypeNames.get(SQLType);
    }

    /**
     * Used to initialise the paramaters array to the correct size.
     */
    private int countQuestionMarks(String statement) {
        int count=0;
        for (char c: statement.toCharArray())
            if (c=='?')
                count++;
        return count;
    }

    

    /**
     * Generate a new SQLStatement with the query string specified. Note that it
     * is highly recommended never to embed values into the query string directly.
     *
     * Instead use a ? for each value and use the Set methods for each type. This
     * will ensure that the SQL types are correctly entered and special characters
     * escaped, ensuring no SQL injection attacks can be performed.
     *
     * @param statement The SQL query string.
     */
    public SQLStatement(String statement) {
        if (statement == null)
            throw new NullPointerException("Cannot create an SQLStatement with no statement!");
        this.statement = statement;
        int numParams = countQuestionMarks(statement);
        params = new ArrayList<SQLParam>(numParams);
        for (int i=0;i<numParams;i++)
            params.add(null);
    }

    /**
     * Generate a new SQLStatement with the query string specified. Note that it
     * is highly recommended never to embed values into the query string directly.
     *
     * Instead use a ? for each value and use the Set methods for each type. This
     * will ensure that the SQL types are correctly entered and special characters
     * escaped, ensuring no SQL injection attacks can be performed.
     *
     * @param statement The SQL query string.
     * @param SQLQueryFlags The query flags (i.e. Statement.RETURN_GENERATED_KEYS to
     *    be used for running this query).
     */
    public SQLStatement(String statement, int SQLQueryFlags) {
        if (statement == null)
            throw new NullPointerException("Cannot create an SQLStatement with no statement!");
        this.statement = statement;
        this.SQLQueryFlags = SQLQueryFlags;
        int numParams = countQuestionMarks(statement);
        params = new ArrayList<SQLParam>(numParams);
        for (int i=0;i<numParams;i++)
            params.add(null);
    }

    /**
     * The copy constructor generates a new SQLStatement identical to but independant
     * of the passed in SQLStatement.
     */
    SQLStatement(SQLStatement toCopy) {
        this.statement = toCopy.statement;
        this.params = new ArrayList(toCopy.params);
        this.SQLQueryFlags = toCopy.SQLQueryFlags;
    }

    /**
     * 
     * Add an extra string to the end of the existing query statement. This will also
     * increase the number of parameters being looked for if required but will not add
     * whitespace to the strings, they will be used concatonated and exactly as given.
     *
     * @param extraStatement The extra text to add to the end of the query string
     */
    public void addToStatement(String extraStatement) {
        statement = statement+extraStatement;
        int numParams = countQuestionMarks(extraStatement);
        for (int i=0;i<numParams;i++)
            params.add(null);
    }

    /**
     * Validates the SQLStatement to ensure that all parameters have been filled in.
     *
     * Attempting to send an SQLStatement that is not valid will produce an immediate
     * exception.
     */
    public boolean isValid() {
        for (SQLParam p: params)
            if (p==null)
                return false;
        return true;
    }

    /**
     * Returns the SQL Query Flags (i.e. Statement.RETURN_GENERATED_KEYS) associated
     * with this query.
     * 
     * @return The SQL Query flags that will be used
     */
    public int getSQLQueryFlags() {
        return SQLQueryFlags;
    }

    /**
     * Sets the SQL Query Flags (i.e. Statement.RETURN_GENERATED_KEYS) associated
     * with this query.
     *
     * @param SQLQueryFlags The SQL Query flags that will be used.
     */
    public void setSQLQueryFlags(int SQLQueryFlags) {
        this.SQLQueryFlags = SQLQueryFlags;
    }

    /**
     * Set the designated paramater to the given value
     *
     * @param parameterIndex The index of the paramater to set
     * @param x The value to set
     */
    public void setBoolean(int parameterIndex, boolean x) {
        params.set(parameterIndex-1, new SQLParam(java.sql.Types.BOOLEAN, x));
    }

    /**
     * Returns the value stored at the requested parameter index. This is mostly useful
     * in callback functions to determine what query is being processed.
     *
     * @param parameterIndex The index of the parameter to query
     * @return The value stored at that index
     * @throws com.zero_separation.pds.sql.SQLStatement.TypeMismatchException If the parameter was not of the correct type
     */
    public boolean getBoolean(int parameterIndex) throws TypeMismatchException {
        if (params.get(parameterIndex-1).getType() != java.sql.Types.BOOLEAN)
            throw new TypeMismatchException("Parameter "+parameterIndex+" type "+getSQLTypeName(params.get(parameterIndex-1).getType())+" not of type BOOLEAN");
        return (Boolean)params.get(parameterIndex-1).getOb();
    }

    /**
     * Set the designated paramater to the given value
     *
     * @param parameterIndex The index of the paramater to set
     * @param x The value to set
     */
    public void setFloat(int parameterIndex, float x) {
        params.set(parameterIndex-1, new SQLParam(java.sql.Types.FLOAT, x));
    }

    /**
     * Returns the value stored at the requested parameter index. This is mostly useful
     * in callback functions to determine what query is being processed.
     *
     * @param parameterIndex The index of the parameter to query
     * @return The value stored at that index
     * @throws com.zero_separation.pds.sql.SQLStatement.TypeMismatchException If the parameter was not of the correct type
     */
    public float getFloat(int parameterIndex) throws TypeMismatchException {
        if (params.get(parameterIndex-1).getType() != java.sql.Types.FLOAT)
            throw new TypeMismatchException("Parameter "+parameterIndex+" type "+getSQLTypeName(params.get(parameterIndex-1).getType())+" not of type FLOAT");
        return (Float)params.get(parameterIndex-1).getOb();
    }

    /**
     * Set the designated paramater to the given value
     *
     * @param parameterIndex The index of the paramater to set
     * @param x The value to set
     */
    public void setInt(int parameterIndex, int x) {
        params.set(parameterIndex-1, new SQLParam(java.sql.Types.INTEGER, x));
    }

    /**
     * Returns the value stored at the requested parameter index. This is mostly useful
     * in callback functions to determine what query is being processed.
     *
     * @param parameterIndex The index of the parameter to query
     * @return The value stored at that index
     * @throws com.zero_separation.pds.sql.SQLStatement.TypeMismatchException If the parameter was not of the correct type
     */
    public int getInt(int parameterIndex) throws TypeMismatchException {
        if (params.get(parameterIndex-1).getType() != java.sql.Types.INTEGER)
            throw new TypeMismatchException("Parameter "+parameterIndex+" type "+getSQLTypeName(params.get(parameterIndex-1).getType())+" not of type INTEGER");
        return (Integer)params.get(parameterIndex-1).getOb();
    }

    /**
     * Set the designated paramater to the given value
     *
     * @param parameterIndex The index of the paramater to set
     * @param x The value to set
     */
    public void setLong(int parameterIndex, long x) {
        params.set(parameterIndex-1, new SQLParam(java.sql.Types.BIGINT, x));
    }

    /**
     * Returns the value stored at the requested parameter index. This is mostly useful
     * in callback functions to determine what query is being processed.
     *
     * @param parameterIndex The index of the parameter to query
     * @return The value stored at that index
     * @throws com.zero_separation.pds.sql.SQLStatement.TypeMismatchException If the parameter was not of the correct type
     */
    public long getLong(int parameterIndex) throws TypeMismatchException {
        if (params.get(parameterIndex-1).getType() != java.sql.Types.BIGINT)
            throw new TypeMismatchException("Parameter "+parameterIndex+" type "+getSQLTypeName(params.get(parameterIndex-1).getType())+" not of type BIGINT");
        return (Long)params.get(parameterIndex-1).getOb();
    }

    /**
     * Set the designated paramater to an SQL NULL value
     *
     * @param parameterIndex The index of the paramater to set
     * @param sqlType A value from java.sql.Types
     */
    public void setNull(int parameterIndex,  int sqlType) {
        params.set(parameterIndex-1, new SQLParam(sqlType, null));
    }

    /**
     * Set the designated paramater to the given value
     *
     * @param parameterIndex The index of the paramater to set
     * @param x The value to set
     */
    public void setRowId(int parameterIndex, RowId x) {
        params.set(parameterIndex-1, new SQLParam(java.sql.Types.ROWID, x));
    }

    /**
     * Returns the value stored at the requested parameter index. This is mostly useful
     * in callback functions to determine what query is being processed.
     *
     * @param parameterIndex The index of the parameter to query
     * @return The value stored at that index
     * @throws com.zero_separation.pds.sql.SQLStatement.TypeMismatchException If the parameter was not of the correct type
     */
    public RowId getRowId(int parameterIndex) throws TypeMismatchException {
        if (params.get(parameterIndex-1).getType() != java.sql.Types.ROWID)
            throw new TypeMismatchException("Parameter "+parameterIndex+" type "+getSQLTypeName(params.get(parameterIndex-1).getType())+" not of type ROWID");
        return (RowId)params.get(parameterIndex-1).getOb();
    }

    /**
     * Set the designated paramater to the given value
     *
     * @param parameterIndex The index of the paramater to set
     * @param x The value to set
     */
    public void setString(int parameterIndex, String x) {
        params.set(parameterIndex-1, new SQLParam(java.sql.Types.VARCHAR, x));
    }

    /**
     * Returns the value stored at the requested parameter index. This is mostly useful
     * in callback functions to determine what query is being processed.
     *
     * @param parameterIndex The index of the parameter to query
     * @return The value stored at that index
     * @throws com.zero_separation.pds.sql.SQLStatement.TypeMismatchException If the parameter was not of the correct type
     */
    public String getString(int parameterIndex) throws TypeMismatchException {
        if (params.get(parameterIndex-1).getType() != java.sql.Types.VARCHAR)
            throw new TypeMismatchException("Parameter "+parameterIndex+" type "+getSQLTypeName(params.get(parameterIndex-1).getType())+" not of type VARCHAR");
        return (String)params.get(parameterIndex-1).getOb();
    }

    /**
     * Set the designated paramater to the given value
     *
     * @param parameterIndex The index of the paramater to set
     * @param x The value to set
     */
    public void setDate(int parameterIndex, Date x) {
        params.set(parameterIndex-1, new SQLParam(java.sql.Types.DATE, x));
    }

    /**
     * Returns the value stored at the requested parameter index. This is mostly useful
     * in callback functions to determine what query is being processed.
     *
     * @param parameterIndex The index of the parameter to query
     * @return The value stored at that index
     * @throws com.zero_separation.pds.sql.SQLStatement.TypeMismatchException If the parameter was not of the correct type
     */
    public Date getDate(int parameterIndex) throws TypeMismatchException {
        if (params.get(parameterIndex-1).getType() != java.sql.Types.DATE)
            throw new TypeMismatchException("Parameter "+parameterIndex+" type "+getSQLTypeName(params.get(parameterIndex-1).getType())+" not of type DATE");
        return (Date)params.get(parameterIndex-1).getOb();
    }

    /**
     * Set the designated paramater to the given value
     *
     * @param parameterIndex The index of the paramater to set
     * @param x The value to set
     */
    public void setTime(int parameterIndex, Time x) {
        params.set(parameterIndex-1, new SQLParam(java.sql.Types.TIME, x));
    }

    /**
     * Returns the value stored at the requested parameter index. This is mostly useful
     * in callback functions to determine what query is being processed.
     *
     * @param parameterIndex The index of the parameter to query
     * @return The value stored at that index
     * @throws com.zero_separation.pds.sql.SQLStatement.TypeMismatchException If the parameter was not of the correct type
     */
    public Time getTime(int parameterIndex) throws TypeMismatchException {
        if (params.get(parameterIndex-1).getType() != java.sql.Types.TIME)
            throw new TypeMismatchException("Parameter "+parameterIndex+" type "+getSQLTypeName(params.get(parameterIndex-1).getType())+" not of type TIME");
        return (Time)params.get(parameterIndex-1).getOb();
    }

    /**
     * Set the designated paramater to the given value
     *
     * @param parameterIndex The index of the paramater to set
     * @param x The value to set
     */
    public void setTimestamp(int parameterIndex, Timestamp x) {
        params.set(parameterIndex-1, new SQLParam(java.sql.Types.TIMESTAMP, x));
    }

    /**
     * Returns the value stored at the requested parameter index. This is mostly useful
     * in callback functions to determine what query is being processed.
     *
     * @param parameterIndex The index of the parameter to query
     * @return The value stored at that index
     * @throws com.zero_separation.pds.sql.SQLStatement.TypeMismatchException If the parameter was not of the correct type
     */
    public Timestamp getTimestamp(int parameterIndex) throws TypeMismatchException {
        if (params.get(parameterIndex-1).getType() != java.sql.Types.TIMESTAMP)
            throw new TypeMismatchException("Parameter "+parameterIndex+" type "+getSQLTypeName(params.get(parameterIndex-1).getType())+" not of type TIMESTAMP");
        return (Timestamp)params.get(parameterIndex-1).getOb();
    }

    /**
     * Set the designated paramater to the given value
     *
     * @param parameterIndex The index of the paramater to set
     * @param x The value to set
     */
    public void setURL(int parameterIndex, URL x) {
        params.set(parameterIndex-1, new SQLParam(java.sql.Types.DATALINK, x));
    }

    /**
     * Returns the value stored at the requested parameter index. This is mostly useful
     * in callback functions to determine what query is being processed.
     *
     * @param parameterIndex The index of the parameter to query
     * @return The value stored at that index
     * @throws com.zero_separation.pds.sql.SQLStatement.TypeMismatchException If the parameter was not of the correct type
     */
    public URL getURL(int parameterIndex) throws TypeMismatchException {
        if (params.get(parameterIndex-1).getType() != java.sql.Types.DATALINK)
            throw new TypeMismatchException("Parameter "+parameterIndex+" type "+getSQLTypeName(params.get(parameterIndex-1).getType())+" not of type DATALINK");
        return (URL)params.get(parameterIndex-1).getOb();
    }

    /**
     * Returns the query string used to construct this SQLStatement.
     *
     * @return The query string
     */
    public String getStatement() {
        return statement;
    }

    @Override
    public String toString() {
        StringBuilder ret = new StringBuilder( "SQL Query: " );
        ret.append(statement);
        for (SQLParam p: params) {
            ret.append("\n\t");
            if (p==null)
                ret.append("Not set yet");
            else
                ret.append(p.toString());
        }
        return ret.toString();
    }

    @Override
    public boolean equals(Object obj) {
        if (obj == null) {
            return false;
        }
        if (getClass() != obj.getClass()) {
            return false;
        }
        final SQLStatement other = (SQLStatement) obj;
        if ((this.statement == null) ? (other.statement != null) : !this.statement.equals(other.statement)) {
            return false;
        }

        if (params.size() != other.params.size())
            return false;
        for (int i=0;i<params.size();i++)
            if ((params.get(i) == null) ? (other.params.get(i) != null) : !params.get(i).equals(other.params.get(i)))
                return false;

        return true;
    }

    @Override
    public int hashCode() {
        int hash = statement.hashCode();
        int x=3;
        for (SQLParam p: params) {
            hash += x*p.hashCode();
            x+=3;
        }
        return hash;
    }


    /**
     * Thrown by the query functions if asked for a parameter with a type not matching
     * that asked for.
     */
    public static class TypeMismatchException extends Exception {

        public TypeMismatchException(String message) {
            super(message);
        }
        
    }
}
